-- SET results to file with no headers in Query Analyzer before execution
-- modified to add tracking insert to all procs with found count > 1 regardless of date (for apollo)
if @@servername <> 'CS234-LTP' RAISERROR ('WrongServer',20,1) WITH LOG
go

declare @dbName varchar(128)
	, @srvName varchar(128)
	, @lnkSrvName varchar(128)
set @dbName = 'alerts' -- need this to build list of procedures NOT found in cache
set @srvName = 'apollo' -- need this to build list of procedures NOT found in cache
set @lnkSrvName = 'lnkbap'


declare @PullDir varchar(128)
	, @notUsedDir varchar(128)
	, @getNewList tinyint
	, @sql nvarchar(2000)

set @getNewList = 1 -- 1 for true (move new files into notUserdDir 0 for skip get and use existing files in local folder

--- Shouldn't need to change anything belwo here in script
-- make sure trailing whack included for both if getNewList set to 1!
set @notUsedDir = 'C:\notfound\' -- where to put the scrips identified on the local server
set @pullDir = '\\CS234-02\C$\PULL\' + @srvName + '\' + @dbName + '\Procedures\' -- where to get scripts for identified procedures 

select 'use ' + @dbName + char(13) + char(10)
       + 'GO' + char(13) + char(10)
       + 'set quoted_identifier off'  + char(13) + char(10)
       + 'GO' + char(13) + char(10)
       + 'if object_id(''dbo.UnusedProcExecutionLog'',''U'') is null' + char(13) + char(10)
       + '	begin' + char(13) + char(10)
       + '		create table ' + @dbName + '.dbo.UnusedProcExecutionLog' + char(13) + char(10)
       + '			(ProcName varchar(128)' + char(13) + char(10)
       + '			, LoginName varchar(128)' + char(13) + char(10)
       + '			, HostName varchar(128)' + char(13) + char(10)
       + '			, AppName varchar(128)' + char(13) + char(10)
       + '			, ExecDt datetime ' + char(13) + char(10)
       + '  		constraint dft_UnusedProcExecutionLog__ExecDt default (getdate()))' + char(13) + char(10)
       + '	end' + char(13) + char(10)
       + '' + char(13) + char(10)
       + 'grant select, insert on dbo.UnusedProcExecutionLog to public' + char(13) + char(10)
       + Space(1) + char(13) + char(10)
       + 'GO' + char(13) + char(10)
       + Space(1) + char(13) + char(10)
       + 'SET QUOTED_IDENTIFIER OFF' + char(13) + char(10) 
       + Space(1) + char(13) + char(10)
       + 'GO' + char(13) + char(10) 
       + Space(1) + char(13) + char(10)
       + 'SET ANSI_NULLS ON' + char(13) + char(10)  
       + Space(1) + char(13) + char(10)
       + 'GO' + char(13) + char(10) 
       + Space(1) + char(13) + char(10)
       + 'if exists (select * from dbo.sysobjects' + char(13) + char(10)  
       + 'where id = object_id(N''[dbo].[instrg_UnusedProcExecutionLog]'')' + char(13) + char(10)  
       + 'and OBJECTPROPERTY(id, N''IsTrigger'') = 1)' + char(13) + char(10)
       + char(9) + 'drop trigger [dbo].[instrg_UnusedProcExecutionLog]' + char(13) + char(10) 
       + Space(1) + char(13) + char(10)
       + 'GO' + char(13) + char(10) 
       + Space(1) + char(13) + char(10)
       + 'CREATE TRIGGER instrg_UnusedProcExecutionLog' + char(13) + char(10) 
       + 'ON dbo.UnusedProcExecutionLog' + char(13) + char(10) 
       + 'FOR INSERT' + char(13) + char(10)  
       + 'AS' + char(13) + char(10)  
       + 'BEGIN' + char(13) + char(10) 
       + '	set nocount on' + char(13) + char(10) 
       + '	exec sysmon.dbo.safe_sendmail' + char(13) + char(10) 
       + '		@recipients = ''DBAs@wallst.com''' + char(13) + char(10) 
       + '		, @subject = ''Usage detected for a suspected ' + @DBName + ' db unused procedure''' + char(13) + char(10) 
       + '		, @Query = ''select * from ' + @DBName + '.dbo.UnusedProcExecutionLog''' + char(13) + char(10) 
       + 'END' + char(13) + char(10) 
       + Space(1) + char(13) + char(10)
       + 'GO' + char(13) + char(10) 
       + Space(1) + char(13) + char(10)
       + 'SET QUOTED_IDENTIFIER OFF' + char(13) + char(10)  
       + Space(1) + char(13) + char(10)
       + 'GO' + char(13) + char(10) 
       + Space(1) + char(13) + char(10)
       + 'SET ANSI_NULLS ON' + char(13) + char(10)  
       + Space(1) + char(13) + char(10)
       + 'GO' + char(13) + char(10) 
       + Space(1) + char(13) + char(10)


declare @notUsed_id int
	, @fName_id int
	, @createproc_id int
	, @as_id int
	, @go_id int
	, @fName varchar(128)
	, @cmd varchar(255)
	, @DblQuote char(1)
	, @SnglQuote char(1)

if object_id('tempdb.dbo.#notUsedList','U') is not null
	drop table #notUsedList
if object_id('tempdb.dbo.#fList','U') is not null
	drop table #fList
if object_id('tempdb.dbo.#filein','U') is not null
	drop table #filein
if object_id('tempdb.dbo.#fileout','U') is not null
	drop table #fileout
if object_id('tempdb.dbo.#parsingProblems','U') is not null
	drop table #parsingProblems

create table #notUsedList (id int identity(1,1) primary key, pName varchar(255), crdate datetime, foundCount int)
create table #fList (id int identity(1,1) primary key, fName varchar(255))
create table #filein (id int identity(1,1) primary key, line varchar(255))
create table #fileout (id int identity(1,1) primary key, line varchar(255) not null)
create table #parsingProblems (id int identity(1,1) primary key, createproc_id int, as_id int, go_id int, problem varchar(30), fName varchar(255))

set nocount on

select '/**********************************************************************************************'
select 'generated by script "AlterTableConversionFrom PULL.sql"'

if @getNewList = 1
	begin

		-- clear out working dir
		set @cmd = 'del ' + @notUsedDir + '*.* /Q'
		exec master.dbo.xp_cmdshell @cmd 

		set @sql = 'select o.name, o.crdate, isnull(p.foundCount,0) from ' + @lnkSrvName + '.' + @dbName + '.dbo.sysobjects o '
             + 'left join ' + @lnksrvName + '.admin.dbo.ProceduresFoundInCache p '
             + 'on o.name = p.ProcedureName ' 							
             + 'and p.DatabaseName = ''' + @dbName + ''' '
             + 'and p.FoundCount > 1 '
		         + 'where o.type = ''P'' '
             + 'and o.name not like ''dt_%'' '
             + 'and p.ProcedureName is null '
             + 'order by o.Name'

		insert into #notUsedList
		exec sp_executesql @sql

    if exists (select pName from #notUsedList
 		           where crdate >= getdate() - 90
 		           and foundcount > 1)
			begin
				select '-- review these procs to see if they should be included. (changed within last 90 days)'
				select pName from #notUsedList
				where crdate >= getdate() - 90
				and foundcount > 1

				delete #notUsedList
				where crdate >= getdate() - 90
				and foundcount > 1
			end

		select @notUsed_id = min(id) 
		from #notUsedList

		while @notUsed_id is not null
			begin

				select @cmd = 'copy ' + @pullDir + 'dbo.' + pName + '.PRC ' + @notUsedDir
				from #notUsedList
				where id = @notUsed_id

				exec master.dbo.xp_cmdshell @cmd, no_output
				select @cmd

				select @notUsed_id = min(id) 
				from #notUsedList
				where id > @notUsed_id

			end -- @NotUsed_Id not null
	end

insert #fList (fName)
exec master.dbo.xp_cmdshell 'dir c:\notfound\*.PRC /B'

select @fName_id = min(id) from #fList

select '**********************************************************************************************/'

while @fName_id is not null
	begin
		select @fName = fName
			, @Cmd = 'type ' + @notUsedDir + @fName 
		from #fList where id = @fName_id

		insert #filein
		exec master.dbo.xp_cmdshell @Cmd

		update #filein 
		set line = ''
		where line is null

		select @createproc_id = id 
		from #filein 
		where replace(line,char(9),char(32)) like 'create %'
    and rtrim(ltrim(substring(replace(line,char(9),char(32)),7,datalength(replace(line,char(9),char(32)))))) like 'proc%'

		if @createproc_id is null
			insert #parsingProblems (createproc_id, as_id, go_id, problem, fName) 
			values (@createproc_id, @as_id, @go_id, '"create" not found. Skipping!', @fName)
		else
			begin

				-- try to find the "as" on it's own line
				select @as_id = min(id)
				from #filein
				where left(rtrim(ltrim(line)),2) = 'as'
				and id > @createproc_id

				-- try to find the "as" at the end of the create proc line
				if @as_id is null
				and (select right(line,3)
					   from #filein
					   where id = @createproc_id) = ' as'
						set @as_id = @createproc_id

				--if didn't find it create the alter statement and note to add code manually later
				if @as_id is null
					insert #parsingProblems (createproc_id, as_id, go_id, problem, fName) 
					values (@createproc_id, @as_id, @go_id, '"as" not found. Create alter!', @fName)

				select @go_id = min(id)
				from #filein
				where line = 'go'
				and id > @createproc_id
		
				if @go_id is null
						insert #parsingProblems (createproc_id, as_id, go_id, problem, fName) 
						values (@createproc_id, @as_id, @go_id, '"go" not found. Skipping!', @fName)
				else
					begin
						update #filein
						set line = replace(line,'create','alter')
						where id = @createproc_id	

						insert #fileout (line)
						values('print ''' + @fname + '''')
						insert #fileout (line)
						values('GO')		

						if @as_id is null
							begin
								-- add a line that will cause execution of alter to fail until it is fixed
								insert #fileout (line) 
								values ('Must manually move insert into alter and remove this line before running script')
								insert #fileout (line)
								values('-- bw 12-2003')
								insert #fileout (line)
								values('-- This procedure does not seem to be used. To verify that')
								insert #fileout (line)
								values('-- in fact it is used insert a row indicating details of')
								insert #fileout (line)
								values('-- the call upon any execution of this procedure.')
								insert #fileout (line)
								values('set nocount on -- don''t ad a result set to what the proc might return')
								insert #fileout (line)
								values('insert ' + @dbName + '.dbo.UnusedProcExecutionLog')
								insert #fileout (line)
								values('(ProcName, LoginName, HostName, AppName)')
								insert #fileout (line)
								values('select object_name(@@PROCID), suser_sname(), host_name() , app_name( )')
								insert #fileout (line)
								values('set nocount off -- put it back the way it was')
								insert #fileout (line)
								values('')
								insert #fileout (line)
								select line from #filein where id between @createproc_id and @go_id
							end
						else
								begin
									insert #fileout (line)
									select line from #filein where id between @createproc_id and @as_id
			
									insert #fileout (line)
									values('-- bw 12-2003')
									insert #fileout (line)
									values('-- This procedure does not seem to be used. To verify that')
									insert #fileout (line)
									values('-- in fact it is used insert a row indicating details of')
									insert #fileout (line)
									values('-- the call upon any execution of this procedure.')
									insert #fileout (line)
									values('set nocount on -- don''t ad a result set to what the proc might return')
									insert #fileout (line)
									values('insert ' + @dbName + '.dbo.UnusedProcExecutionLog')
									insert #fileout (line)
									values('(ProcName, LoginName, HostName, AppName)')
									insert #fileout (line)
									values('select object_name(@@PROCID), suser_sname(), host_name() , app_name( )')
									insert #fileout (line)
									values('set nocount off -- put it back the way it was')
									insert #fileout (line)
									values('')							
									insert #fileout (line)
									select line from #filein where id between @as_id + 1 and @go_id
								end -- as_id is not null

						select line from #fileout

						set @createproc_id = null
						set @as_id = null
						set @go_id = null

					end -- @go_id is not null
		
			end -- @createproc_id is not null

		truncate table #filein

		truncate table #fileout

		select @fName_id = min(id) 
		from #fList
		where id > @fName_id

	end	-- @fName_id is not null

if exists (select * from #parsingProblems)
	begin
		print '/*************************************************************************************************'
		print 'The following files must be manually edited - there was a parsing problem.'
		select * from #parsingProblems
		print '*************************************************************************************************/'
	end